﻿using Tier3CommonDALLifeIt;
using System;
using System.Linq;
using System.Collections.Generic;

namespace DALLifeIt.DALLifeIt
{
    public class PromocionVWDAL: Linq3tierDALBase
    {
        public V_RFID_HISTORIAL_COMPRAS buscarMaximo(string codigo)
        {
            //var articulo = GetTables<V_RFID_ARTICULOS>();

            //var cliente = GetTables<V_RFID_CLIENTES>();

            var historial = GetTables<V_RFID_HISTORIAL_COMPRAS>();

            var query = (from h in historial
                         where h.NIF20 == codigo
                         orderby h.TOTAL descending
                         select h
                         );

            List<V_RFID_HISTORIAL_COMPRAS> listaHistorial = query.ToList();

            return listaHistorial.First();
        }

        public List<V_RFID_ARTICULOS> buscarPromocion(string subfamilia) {
            var articulo = GetTables<V_RFID_ARTICULOS>();

            var query = (from a in articulo
                         where a.SUB_FAMILIA == subfamilia
                         select a
                        ).OrderByDescending(n => n.TIER_PRECIO);

            List<V_RFID_ARTICULOS> listaArticulo = query.ToList();

            return listaArticulo;
        }

        public V_RFID_CLIENTES buscarCliente(string codigo) {

            var cliente = GetTables<V_RFID_CLIENTES>();

            var query = (from c in cliente
                         where c.CODIGO_CLIENTE == codigo
                         select c
                        );

            List<V_RFID_CLIENTES> listaCliente = query.ToList();

            return listaCliente.First();
        }

        public string buscarSubfamilia(string codigo)
        {
            var historial = GetTables<V_RFID_HISTORIAL_COMPRAS>();

            var query = (from h in historial
                         where h.NIF20 == codigo
                         group h by h.SUB_FAMILIA into num
                         select new
                         {
                             Key = num.Key,
                             Total = num.Count(),
                         }
                         ).OrderByDescending(n => n.Total);

            return query.First().Key.ToString();
        }

        public List<string> crossSelling(string subfamilia)
        {
            var cross = GetTables<RFID_SUBFAMILIAS_REL>();

            var query = (from r in cross
                         where r.SUB_FAMILIA == subfamilia
                         select r.SUB_FAM_RELAC
                        );

            var query2 = (from q in cross
                          where q.SUB_FAM_RELAC == query.First().ToString()
                          where q.SUB_FAMILIA != subfamilia
                          select q.SUB_FAMILIA
                         );

            return query2.ToList();
        }
    }
}
